﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SeDuce.IDAL;
using System.Data.SqlClient;

namespace SeDuce.DAL
{
    public class TypeDAL : ITypeDAL
    {
        #region SQL
        public const string sqlGetTypes = "SELECT * FROM TbType";
        const string sqlUpdateType = "UPDATE TbType SET TypeName = {0},Sort = {1} WHERE (TypeID = {2})";
        const string sqlInsertType = "INSERT INTO TbType (TypeName, Sort) VALUES ({0},{1})";
        const string sqlDeleteType = "Delete from TbType where TypeID={0}";
        #endregion

        #region Variables
        private static DataAccessObjectBase _dao = null;
        public static DataAccessObjectBase DAO
        {
            get
            {
                if (_dao == null)
                    _dao = new DataAccessObjectBase();
                return _dao;
            }
            set
            {
                _dao = value;
            }
        }
        #endregion

        public System.Data.DataTable GetTypes()
        {
            return DAO.Select(sqlGetTypes);
        }

        public bool UpdateType(Model.Type type)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@TypeName", type.TypeName),
                new SqlParameter ("@Sort", type.Sort),
                new SqlParameter ("@TypeID", type.TypeID)
            };
            string sql = string.Format(sqlUpdateType, "@TypeName", "@Sort", "@TypeID");

            return DAO.ExecuteSql(sql, sp);
        }

        public bool UpdateTypes(List<Model.Type> types)
        {
            Dictionary<SqlParameter[], string> updateSqls = new Dictionary<SqlParameter[], string>();
            for (int i = 0; i < types.Count; i++)
            {
                SqlParameter[] parms = new SqlParameter[] { 
                    new SqlParameter ("@TypeName",types[i].TypeName),
                    new SqlParameter ("@Sort",types[i].Sort),
                    new SqlParameter ("@TypeID",types[i].TypeID)
                  };
                string sql = string.Format(sqlUpdateType, "@TypeName", "@Sort", "@TypeID");
                updateSqls.Add(parms, sql);
            }
            return DAO.ExecuteSqlTran(updateSqls);
        }

        public bool InsertType(Model.Type type)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@TypeName", type.TypeName),
                new SqlParameter ("@Sort", type.Sort)
            };
            string sql = string.Format(sqlInsertType, "@TypeName", "@Sort");

            return DAO.ExecuteSql(sql, sp);
        }

        public bool DeleteType(int typeID)
        {
            SqlParameter[] sp = new SqlParameter[] { 
                new SqlParameter ("@TypeID",typeID),
    
            };
            string sql = string.Format(sqlDeleteType, "@TypeID");
            return DAO.ExecuteSql(sql, sp);
        }
    }
}
